package dbLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;

import javax.swing.JOptionPane;

import modelLayer.Customer;
import modelLayer.Product;

public class DBCustomers implements IFDBCustomers {

	protected Connection con;

	public DBCustomers() {
		con = DbConnection.getInstance().getDBcon();
	}

	@Override
	public int insertCustomer(Customer customer) throws Exception {
		String query = "INSERT INTO customers (firstName, lastName, cpr, address, phoneNumber, email) VALUES("
				+ " '"
				+ customer.getFirstName()
				+ "',"
				+ " '"
				+ customer.getLastName()
				+ "',"
				+ " '"
				+ customer.getCpr()
				+ "',"
				+ " '"
				+ customer.getAddress()
				+ "',"
				+ " '"
				+ customer.getPhoneNumber()
				+ "',"
				+ " '"
				+ customer.getEmail()
				+ "');";
		int rc = -1;
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Inserting Customer ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			System.out.println("SUCCESS: Customer Inserted!");
		}// end try
		catch (SQLException ex) {
			JOptionPane.showMessageDialog(null, "There was an error, check if there is not another Customer with the same cpr or phone number, these two must be unique for each customer.", "Insert Error!", JOptionPane.YES_NO_OPTION);
			System.out.println("ERROR:" + ex.getMessage());
		}
		return rc;
	}

	@Override
	public int updateCustomer(Customer customer, Customer newCustomer)
			throws Exception {
		String query = "UPDATE customers SET ";
		if (!newCustomer.getFirstName().equals("")) {
			query += "firstName='" + newCustomer.getFirstName() + "' ";
		}
		if (!newCustomer.getLastName().equals("")) {
			query += ", lastName='" + newCustomer.getLastName() + "' ";
		}
		if (!newCustomer.getCpr().equals("")) {
			query += ", cpr='" + newCustomer.getCpr() + "' ";
		}
		if (!newCustomer.getAddress().equals("")) {
			query += ", address='" + newCustomer.getAddress() + "' ";
		}
		if (!newCustomer.getPhoneNumber().equals("")) {
			query += ", phoneNumber='" + newCustomer.getPhoneNumber() + "' ";
		}
		if (!newCustomer.getEmail().equals("")) {
			query += ", email=" + newCustomer.getEmail();
		}
		query += " WHERE customerId='" + customer.getCustomerId() + "'";
		int rc = -1;
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Updating Customer ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			System.out.println("SUCCESS: Customer Updated!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		return rc;
	}

	@Override
	public int deleteCustomer(Customer customer) throws Exception {
		String query = "";
		if (customer.getCustomerId() >= 0) {
			query = "DELETE FROM customers WHERE customerId='"
					+ customer.getCustomerId() + "';";
		} else {
			query = "DELETE FROM products WHERE ";
			if (!customer.getFirstName().equals("")) {
				query += "firstName='" + customer.getFirstName() + "' ";
			}
			if (!customer.getLastName().equals("")) {
				query += ", lastName='" + customer.getLastName() + "' ";
			}
			if (!customer.getCpr().equals("")) {
				query += ", cpr='" + customer.getCpr() + "' ";
			}
			if (!customer.getAddress().equals("")) {
				query += ", address='" + customer.getAddress() + "' ";
			}
			if (!customer.getPhoneNumber().equals("")) {
				query += ", phoneNumber='" + customer.getPhoneNumber() + "' ";
			}
			if (!customer.getEmail().equals("")) {
				query += ", email=" + customer.getEmail();
			}
		}
		int rc = -1;
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Updating Product ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
			System.out.println("SUCCESS: Product Deleted!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		return rc;
	}

	@Override
	public Customer searchCustomer(Customer customer) throws Exception {
		String query = "";
		if (customer.getCustomerId() >= 0) {
			query = "SELECT * FROM customers WHERE " + "customerId='"
					+ customer.getCustomerId() + "'";
		} else {
			query = "SELECT * FROM customers WHERE ";

			query += "firstName='" + customer.getFirstName() + "'";

			query += " AND " + "lastName='" + customer.getLastName() + "'";

			query += " AND " + "cpr='" + customer.getCpr() + "'";

			query += " AND " + "phoneNumber='" + customer.getPhoneNumber()	+ "'";

			query += " AND " + "address='" + customer.getEmail() + "'";

		}
		ResultSet rs = null;
		LinkedList<Customer> data = new LinkedList<Customer>();
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Searching Customer ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			// rc = stmt.executeUpdate(query);
			rs = stmt.executeQuery(query);

			while (rs.next()) {
				data.add(new Customer(rs.getInt("customerId"), rs
						.getString("firstName"), rs.getString("lastName"), rs
						.getString("cpr"), rs.getString("address"), rs
						.getString("phoneNumber"), rs.getString("email")));
			}
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		if (data.size() == 1) {
			return data.get(0);
		} else {
			return new Customer();
		}
	}

	@Override
	public LinkedList<Customer> getAllCustomers() throws Exception {
		String query = "SELECT * FROM customers";
		
		ResultSet rs = null;
		LinkedList<Customer> data = new LinkedList<Customer>();
		System.out.println("QUERRY : " + query);
		try {
			System.out.println("Getting all Customers ...");
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			// rc = stmt.executeUpdate(query);
			rs = stmt.executeQuery(query);

			while (rs.next()) {
				data.add(new Customer(rs.getInt("customerId"), rs
						.getString("firstName"), rs.getString("lastName"), rs
						.getString("cpr"), rs.getString("address"), rs
						.getString("phoneNumber"), rs.getString("email")));
			}
			stmt.close();
			System.out.println("SUCCESS: Querry executed!");
		}// end try
		catch (SQLException ex) {
			System.out.println("ERROR:" + ex.getMessage());
		}
		return data;
	}

}
